<?php

class Queries {
	
	
	/**
	 * Returns a list of current Content objects for the page
	 *
	 * @param int $pageId
	 * @return array
	 */
	public function loadContents($pageId) {
		$stmt = $this->db->prepare("SELECT contents.*
			FROM
				(SELECT revisions.pageId, parent.name as pageName, revisions.number as revNumber,
					revisions.template, contents.*
				FROM
					pages as page
					INNER JOIN pages as parent ON page.lft BETWEEN parent.lft AND parent.rgt
					INNER JOIN revisions ON parent.id = revisions.pageId
					INNER JOIN contents ON revisions.id = contents.revisionId
				WHERE
					revisions.publish <= NOW() AND
					(revisions.expires IS NULL OR revisions.expires >= NOW())
					AND page.id = 1
				ORDER BY parent.lft DESC, revisions.publish DESC) AS tbl
			GROUP BY field");
		
		$stmt->execute(array($pageId));
		$stmt->setFetchMode(PDO::FETCH_CLASS, "Content");
		return $stmt->fetchAll();
	}
	
	
	/**
	 * Returns a list of Content objects for the page at a specific revision
	 *
	 * @param int $pageId
	 * @param int $revisionId
	 * @return array
	 */
	public function loadContentsByRevision($pageId, $revisionId) {
		$stmt = $this->db->prepare("SELECT contents.*
			FROM
				(SELECT revisions.pageId, parent.name as pageName, revisions.number as revNumber,
					revisions.template, contents.*
				FROM
					pages as page
					INNER JOIN pages as parent ON page.lft BETWEEN parent.lft AND parent.rgt
					INNER JOIN revisions ON parent.id = revisions.pageId
					INNER JOIN contents ON revisions.id = contents.revisionId,
					revisions as thisRevision
				WHERE
					thisRevision.id = ?
					AND
					(revisions.id = thisRevision.id
					OR (revisions.publish <= IF(thisRevision.publish IS NOT NULL, thisRevision.publish, NOW())
						AND (revisions.expires IS NULL
							OR revisions.expires >= IF(thisRevision.publish IS NOT NULL, thisRevision.publish, NOW()))
					))
					AND page.id = ?
				ORDER BY parent.lft DESC, revisions.publish DESC) AS tbl
			GROUP BY field");
		
		$stmt->execute(array($revisionId, $pageId));
		$stmt->setFetchMode(PDO::FETCH_CLASS, "Content");
		return $stmt->fetchAll();
	}
	
	/**
	 * Returns a list of Content objects for the page at a specific date in time
	 *
	 * @param int $pageId
	 * @param int $date
	 * @return array
	 */
	public function loadContentsByDate($pageId, $date) {
		$stmt = $this->db->prepare("SELECT contents.*
			FROM
				(SELECT revisions.pageId, parent.name as pageName, revisions.number as revNumber,
					revisions.template, contents.*
				FROM
					pages as page
					INNER JOIN pages as parent ON page.lft BETWEEN parent.lft AND parent.rgt
					INNER JOIN revisions ON parent.id = revisions.pageId
					INNER JOIN contents ON revisions.id = contents.revisionId
				WHERE
					revisions.publish <= ? AND
					(revisions.expires IS NULL OR revisions.expires >= ?)
					AND page.id = 1
				ORDER BY parent.lft DESC, revisions.publish DESC) AS tbl
			GROUP BY field");
		
		$date = $this->formatDate($date);
		$stmt->execute(array($date, $date, $pageId));
		$stmt->setFetchMode(PDO::FETCH_CLASS, "Content");
		return $stmt->fetchAll();
	}
	
	/**
	 * Loads a list of revisions for a page ordered by created date
	 *
	 * @param int $pageId
	 * @return array
	 */
	public function loadRevisions($pageId) {
		$stmt = $this->db->prepare("SELECT revisions.*
			FROM
				revisions
			WHERE
				pageId = ?
			ORDER BY created");
		
		$stmt->execute(array($pageId));
		$stmt->setFetchMode(PDO::FETCH_CLASS, "Revision");
		return $stmt->fetchAll();
	}
	
	
	public function createPage($name, $userId, $parentId = null) {
		
		if (!$parentId) {
			$stmt = $this->db->query("SELECT MAX(page.rgt) + 1, COUNT(parent.name) - 1 AS depth
				FROM pages as page, pages as parent
				WHERE page.name < ?
				HAVING depth = 0
				ORDER BY page.name DESC");
			
			$stmt->execute(array($this->name));
			$lft = $stmt->fetch(PDO::FETCH_COLUMN, 0)
			or $lft = 1;
			$rgt = $lft + 1;
		}
	}
	
	/**
	 * Deletes a page and all of it's subpages
	 *
	 * @param int $pageId
	 */
	public function deletePage($pageId) {
		$stmt = $this->db->query("SELECT lft, rgt FROM pages WHERE id = ?", $pageId);
		$result = $stmt->fetch();
		$stmt->closeCursor();
		$width = $result['rgt'] - $result['lft'] + 1;
		
		$delStmt = $this->db->prepare("DELETE FROM pages WHERE lft BETWEEN ? AND ?");
		$lftStmt = $this->db->prepare("UPDATE pages SET lft = lft - $width WHERE lft > ?");
		$rgtStmt = $this->db->prepare("UPDATE pages SET rgt = rgt - $width WHERE rgt > ?");
		
		$this->db->query("START TRANSACTION");
		$delStmt->execute(array($result['lft'], $result['rgt']));
		$lftStmt->execute(array($result['rgt']));
		$rgtStmt->execute(array($result['rgt']));
		$this->db->query("COMMIT TRANSACTION");
	}
	
	
	// returns a timestamp as a database time string
	protected static function formatDate($time) {
		return date("Y-m-d H:i:s", $time - self::$timeOffset);
	}
}


?>
